#! /usr/local/bin/perl5

use strict;
use warnings ( FATAL => 'all' );

# use lib "../perllib";

use Data::Dumper;
use File::Basename;
use Readonly;
use POSIX qw(strftime);

use FixInc::DBAccess;
use FixInc::OopOptsDB;
use Feeds::API::File;
use DateTime;
use IPC::Open3;
use FileHandle;

use FixInc::Logger;
use FixInc::UpdateMgr;

use constant SCRIPT_NM => basename($0);

Readonly my $HOST_NM => $ENV{HOST} || `hostname | tr -d '\n'`;
Readonly my $NOW => strftime( '%Y%m%d.%H%M%S', localtime );
Readonly my $TODAY => $ENV{TODAY_CYMD} || substr( $NOW, 0, 8 );

# Turn on output autoflush
$| = 1;

# setup log object
my $l = FixInc::Logger->new( min_level => 'notice' );
my $script_nm = SCRIPT_NM;
$l->notice("=== $script_nm has started === (pid=$$) (ppid=@{[getppid()]})");
$l->info( "CMD:", $0, join( " ", @ARGV ) );

## Parse Jobparams ##
my $defs = FixInc::OopOptsDB->newOptSet(
    help => <<'EOH',
This script downloads xml data from oekb web site https://www.profitweb.at
EOH

    defaults => {
        server          => 'HGCDB1',
        web_usr         => 'wdbo_reuters',
        web_retry       => '3',
        delete_fail     => 'y',
        download_days   => '7',
        date_mode       => '1',
        download_mode   => 'D',          ##I=> initial load(both include code tables and none-code tables
    },                                   ##C=> Get code tables' data
                                         ##D=> Get daily update data
                                         ##T=> Get the text data
                                         ##M=> make up the wrong daily updates
                                         
    optdefs => FixInc::OopOptsDB->newOptDef(
        {   name     => 'web_usr',
            aliases  => ['WS'],
            maxargs  => 1,
            required => 1,
            type     => 'string',
            help     => 'The username of HTTPS login.'
        },
        {   name     => 'web_pass',
            aliases  => ['WP'],
            maxargs  => 1,
            required => 1,
            type     => 'string',
            help     => 'The password of the web login user.'
        },
        {   name     => 'web_proxy',
            aliases  => ['WPR'],
            maxargs  => 1,
            required => 0,
            type     => 'string',
            help     => 'The password of the web login user.'
        },
        {   name     => 'web_retry',
            aliases  => ['WR'],
            maxargs  => 1,
            required => 0,
            type     => 'string',
            help     => 'The curl retry times when it encounters errors.'
        },
        {   name     => 'tbl_url',
            aliases  => ['TU'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The URL of download an Oekb table data.'
        },
        {   name     => 'daily_up_url',
            aliases  => ['DUU'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The URL of download Oekb daily update data.'
        },
        {   name     => 'email_success',
            aliases  => ['ES'],
            required => 0,
            maxargs  => 9,
            type     => 'string',
            help     => 'email address'
        },
        {   name     => 'email_failure',
            aliases  => ['EF'],
            required => 0,
            maxargs  => 9,
            type     => 'string',
            help     => 'email address'
        },
        {   name     => 'download_mode',
            aliases  => ['DM'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'The download mode: I,C,D.'
        },
        {   name     => 'delete_fail',
            aliases  => ['DF'],
            required => 0,
            maxargs  => 1,
            type     => 'string',
            help     => 'If delete downloaded files when failure'
        },
        {   name     => 'holidays',
            aliases  => ['HO'],
            required => 0,
            maxargs  => 30,
            type     => 'string',
            help     => 'The holidays that do not need to download data from website'
        },
        {   name     => 'download_days',
            aliases  => ['DD'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'How many days data are needed.'
        },
        {   name     => 'date_mode',
            aliases  => ['DAM'],
            required => 1,
            maxargs  => 1,
            type     => 'string',
            help     => 'Use datum or log_datum to download daily updates'
        },
    )
);

my $log = FixInc::Logger->new( min_level => 'info' );

my $oops = FixInc::OopOptsDB->new(
    'optset' => $defs,
    'log'    => $log
    )
    or $l->die("Cannot process options: FixInc::OopOptsDB->new");

my $dbh = FixInc::UpdateMgr->connect(
   server   => 'HGCDB1',
   dbset    => '',
   database => 'cds_broker_feed',
   log      => $log,
   implocks => 'y',
   username => 'cds_broker_feed',
   password => 'cds_broker_feed' 
   )
   or die "Cannot connect: $FixInc::DBAccess::errstr";

my $O = $oops->opthash;
$log->paramApply($O);


# create database handlers
# 2) feed

#print "web_usr=\[", $web_usr, "\]\n";
#print "web_pass=\[", $web_pass, "\]\n";

$log->info("Create $O->{database} db handler ...");
my $feed_dbh = Feeds::API::FeedDB->paramApply($O)->connect( %$O, log => $log )
    or $log->die( "'$script_nm': Cannot connect to '$O->{server}': ",
        $FixInc::DBAccess::errstr );

# get CFA database version
my $fdb_version = Feeds::API::FeedDB->feeddb_version(%$O);

# register job info
my $job = Feeds::API::Job->new( action_type => "oekb_xml_download", log => $log)
    or $log->die("Cannot create new Feeds::API::Job: $!");


print "ooooooooooooooooooooooooooooooooooooo\n";
$log->info(localtime);

# Create file handles for in, out and err
my( $cin, $cout, $cerr) = ( new FileHandle(), new FileHandle(), new FileHandle() );

# Open isql in another process
open3( $cin, $cout, $cerr, '/usr/pkgs/sybase/OCS/bin/isql', "-w", "500",
    "-S", "HGCDB1", "-U", "cds_broker_feed", "-P", "cds_broker_feed",)
    || $log->die("can not open a process for isql");

my $sql =<<"SQL";
select t1.contract_id
from cds_broker_feed..p_ent_stat t1, cds_broker_feed..n_gfi t2
where t1.feed_type = 'GFI' and t1.status_cd = 'LOD' 
  and t1.contract_id = t2.oid and t2.obg_asset_rank = 'IDX' and t2.tranche_tier != 'n'
order by t1.status_dt desc
SQL

#my $res = $dbh->selectrow_array($sql);
my $res = $dbh->selectall_hashes($sql);
foreach my $rec (@$res){
    my $contract_id = $rec->{contract_id};
    
    #检索到vendor数据为后面业务流程做准备
    $sql =<<"SQL";
    select * from cds_broker_feed..n_gfi where oid = $contract_id
SQL
    
}

close( $cin );
$log->info(localtime);
print "ooooooooooooooooooooooooooooooooooooo\n";

$job->success();

# to force DESTROY
undef $job;
$feed_dbh->disconnect() if defined $feed_dbh;
$log->notice("=== @{[SCRIPT_NM]} has stopped ===");

__END__
